Configure antivirus software to work with SQL Server

This article contains general guidelines to help you properly configure antivirus software on computers that are running SQL Server in your environment.

We strongly recommend that you individually assess the security risk for each computer that's running SQL Server in your environment. Based on the assessment, you must select the appropriate tools for the security risk level of each computer that's running SQL Server.

Additionally, we recommend that you test the entire system under a full load to measure any changes in stability and performance before you roll out any virus-protection software.

Virus protection software requires some system resources to execute. You must perform testing before and after you install your antivirus software to determine whether there's any performance effect on the computer that's running SQL Server.

Consider the following factors when deciding on anti-malware solutions:

Any server is at some risk of infection. The highest-risk servers generally meet one or more of the following criteria:

Servers that don't meet the criteria for a high-risk server are generally at a lower risk, although not always.

Active virus scanning: This kind of scanning checks incoming and outgoing files for viruses.

Virus sweep software: Virus sweep software scans existing files for file infection. It detects issues after files are infected by a virus. This kind of scanning may cause the following SQL Server database recovery and SQL Server full-text catalog file issues:

If the virus sweep software has opened a database file when SQL Server tries to open the database, the database to which the file belongs might be marked as suspect. SQL Server opens a database when it starts or when a database with Auto-Close enabled was closed and is accessed again. SQL Server database files typically have .mdf, .ldf, or .ndf file name extensions.

If the virus sweep software has a SQL Server full-text catalog file open when Full-Text Search tries to access the file, you may have problems with the full-text catalog.

Vulnerability scanning software: The Microsoft Security Compliance Toolkit includes a set of tools that enable enterprise administrators to perform a wide range of security tasks. These tasks include download, analyze, test, edit, store Microsoft-recommended security configuration baselines for Windows and other Microsoft products, and compare them against other security configurations. To download it, go to Microsoft Security Compliance Toolkit 1.0.

Microsoft also released the Windows Malicious Software Removal Tool to help remove specific, prevalent malicious software from computers. For more information about the Microsoft Windows Malicious Software Removal Tool, see Remove specific prevalent malware with Windows Malicious Software Removal Tool (KB890830).

Note

Windows Server 2016 and later versions automatically enable Windows Defender. Make sure that Windows Defender is configured to exclude Filestream files. Failure to do this can result in decreased backup and restore operations performance. For more information, see Configure and validate exclusions for Windows Defender Antivirus scans.

When you configure your antivirus software settings, make sure that you exclude the following processes (as applicable) from virus scanning.

For an updated list of services and file paths, see Services installed by SQL Server.

Applications installed on a SQL Server computer can load modules into the SQL Server process (sqlservr.exe). The applications use this functionality to run business logic or for intrusion monitoring and protection. To detect if an unknown module or a module from third-party software was loaded into the process memory space, check the output of the sys.dm_os_loaded_modules Dynamic Management View (DMV).

In some cases, applications or drivers may be used to detour SQL Server or Windows code to provide malware protection or monitoring services. However, if such applications or drivers aren't designed correctly, they may cause a wide variety of issues for products like SQL Server. For information about third-party detours or similar techniques in SQL Server, see Detours or similar techniques may cause unexpected behaviors with SQL Server.

This section applies to SQL Server installations running on Windows operating systems, both stand-alone and Failover Cluster Instances (FCI).

When you configure your antivirus software settings, make sure that you exclude the following files or directories (as applicable) from virus scanning. Exclusion may improve SQL Server performance and ensures that the files aren't locked when the SQL Server service must use them. However, if these files become infected, your antivirus software can't detect the infection. For more information about the default file locations for SQL Server, see File Locations for Default and Named Instances of SQL Server.

These files usually have one of the following file name extensions:

By default, the data files are located in the following directories. However, they can be placed in any directory by the database administrators of the system.

Note

and are placeholders.

These files typically have one of the following file name extensions:

By default, the backup folders are located in the following directories. However, backup files can be placed in any directory by the database administrators of the system.

These files usually have the .trc file name extension. These files can be generated when you configure SQL tracing manually or when you enable C2 auditing for the server.

These files have the .sqlaudit file name extension. For more information, see SQL Server Audit (Database Engine).

These files typically have the .sql file name extension and contain Transact-SQL statements.

In essence, the In-Memory OLTP technology has two sets of files:

Files related to natively compiled stored procedures and memory-optimized tables.

The In-memory OLTP files are typically stored in an xtp subfolder under the DATA directory for the instance.

File formats include the following types:

Note

xtp is a prefix used to indicate the association with In-memory OLTP. The placeholder represents either "t" for table or "p" for procedure. The placeholder refers to the database ID of the user database where the memory-optimized object is located. The placeholder indicates the object ID assigned to the memory-optimized object (either the table or the procedure).

Files related to checkpoint and delta files.

Files use the following format:

_MSSQL_DBCC

These are temporary files.

For more information, see Internal database snapshot.

Replication executables and server-side COM objects

Note

The is a placeholder for version-specific information. To specify the correct value, check your installation or search for "Replication and server-side COM objects" in Specifying File Paths. For example, the full path for SQL Server 2022 would be :\Program Files\Microsoft SQL Server\160\COM\.

Starting with SQL Server 2017 CU22 (including SQL 2019 RTM and later versions), if using Transactional Replication and the Distribution Agent is utilizing OLEDB streaming profile, or you're using the -UseOledbStreaming parameter, the Distribution Agent creates temporary files (*.lob) in the AppData folder of the account running the distribution agent where the job is being invoked. For example, C:\Users\\AppData\Temp\*.lob. For prior versions of SQL Server, the default COM folder (already listed) is used.

For more information, see "The distribution agent failed to create temporary files" error message.

Files in the Replication Snapshot folder

The default path for the snapshot files is \Microsoft SQL Server\MSSQL.MSSQLSERVER\MSSQL\ReplData. These files typically have file name extensions such as .sch, .idx, .bcp, .pre, .cft, .dri, .trg, or .prc.

You can run antivirus software on a SQL Server cluster. However, you must make sure that the antivirus software is a cluster-aware version.

Contact your antivirus vendor about cluster-aware versions and interoperability.

If you're running antivirus software on a cluster, make sure that you also exclude these locations from virus scanning:

If you back up the database to a disk or back up the transaction log to a disk, you can exclude the backup files from the virus scanning.

For more information about antivirus considerations on a cluster, see Antivirus software that isn't cluster-aware may cause problems with Cluster Services.

The following Analysis Services directories and processes can be excluded from antivirus scanning.

The is a placeholder for the build ID. For example, a default Analysis Services 2016 instance binary installation location by default is C:\Program Files\Microsoft SQL Server\MSAS13.MSSQLSERVER\OLAP\bin.

When you configure your antivirus software settings, make sure that you exclude the following SSAS files or directories (as applicable) from virus scanning. Excluding the files improves SSAS performance and helps make sure that the files aren't locked when the SQL Server service must use them. However, if these files become infected, your antivirus software can't detect the infection.

The directory that holds all Analysis Services data files is specified in the DataDir property of the instance of Analysis Services. By default, the path of this directory is:

For Analysis Services 2012 and later versions, temporary files during processing are specified by the TempDir property of the instance of Analysis Services. By default, this property is empty. When this property is empty, the default directory is used. The following table shows the Temp path by default.

In Analysis Services 2012 and later versions, the backup file location is the location that is specified by the BackupDir property. The following table shows the backup path for the Analysis Service instance by default:

You can change this directory in the properties of the instance of Analysis Services. Any backup command can point to a different location also. Or, the backup files can be copied elsewhere for restore.

By default, in Analysis Services 2012 and later versions, the log file location is the location that is specified by the LogDir property. By default, the Log path is located as follows:

When you create the partitions, these locations are defined in the Storage location section of the Processing and Storage Locations page of the Partition Wizard. Be sure to exclude those from scanning.

The following processes and directories for the SSIS services are to be excluded from antivirus scanning.

Note

The placeholder refers to the version-specific details.

When you configure your antivirus software settings, make sure that you exclude the following files or directories (as applicable) from virus scanning. This improves the performance of the files and helps make sure that the files aren't locked when the SSIS service must use them. However, if these files become infected, your antivirus software can't detect the infection.

Note

The placeholder refers to the version-specific details.

The following processes and directories for the PolyBase services are to be excluded from anti-virus scanning.

PolyBase Data Movement service (DMS) and Engine services use the same executable with different command line parameters.

When you configure your antivirus software settings, make sure that you exclude the following files or directories (as applicable) from virus scanning. This improves the performance of the files and helps make sure that the files aren't locked when the PolyBase service must use them. However, if these files become infected, your antivirus software can't detect the infection.

The following processes and directories for the SQL Server Reporting Services (SSRS) are to be excluded from antivirus scanning.

The executables to exclude have evolved across different versions of SSRS. The following table lists them according to the SSRS version.

For Power BI Report Server, the following exclusions can be made:

The following table contains information about how to use a firewall with SQL Server: